import pymysql


class Mysql:
    __connection = None

    def __init__(self):
        self.__connection = pymysql.connect(
            host='1.15.241.154',
            port=3306,
            user='lottery_xyyhome_',
            password='d8prBhpjFMSpEB2d',
            database='lottery_xyyhome_',
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor)

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_value, exc_trace):
        self.__connection.commit()
        self.__connection.close()

    def select(self, sql, param=None):
        with self.__connection.cursor() as cursor:
            cursor.execute(sql, param)
            return cursor.fetchall()

    def select_count(self, table_name: str, /, *, where=None) -> int:
        with self.__connection.cursor() as cursor:
            if where is None:
                cursor.execute(f'select count(*) count from {table_name}')
            else:
                cursor.execute(f'select count(*) count from {table_name} where {where}')
            return cursor.fetchone()['count']

    def select_one(self, sql, params=None):
        with self.__connection.cursor() as cursor:
            cursor.execute(sql, params)
            return cursor.fetchone()

    def executemany(self, sql, params=None):
        with self.__connection.cursor() as cursor:
            try:
                num = cursor.executemany(sql, params)
            finally:
                self.__connection.commit()
            return num

    def execute(self, sql, params=None):
        with self.__connection.cursor() as cursor:
            try:
                num = cursor.execute(sql, params)
            finally:
                self.__connection.commit()
            return num

    def commit(self):
        self.__connection.commit()

    def rollback(self):
        self.__connection.rollback()

    def execute_not_commit(self, sql, params=None):
        with self.__connection.cursor() as cursor:
            return cursor.execute(sql, params)

    def reset_auto_increment(self, table_name, id_column):
        """
        重置表的auto_increment为当前表最大id=1,因为使用insert_or_update_sql生成的sql更新数据时，表的auto_increment会增加，如果不需要，可以使用此方法重置一次
        :param table_name: 表名
        :param id_column: 自增字段名，就是查询最大的那个时用到的
        :return:
        """
        with self.__connection.cursor() as cursor:
            try:
                cursor.execute(f'SELECT max({id_column}) max FROM {table_name}')
                max_id = cursor.fetchone()['max']
                cursor.execute(f'alter table {table_name} auto_increment={max_id + 1}')
            finally:
                self.__connection.commit()

    def truncate(self, table_name):
        with self.__connection.cursor() as cursor:
            cursor.execute(f'TRUNCATE {table_name}')


def insert_sql(table_name: str, data: dict) -> str:
    return insert_sql_by_list(table_name, data.keys())


def insert_sql_by_list(table_name: str, columns) -> str:
    cols = ", ".join(f'`{k}`' for k in columns)  # 字段名拼接
    val_cols = ', '.join(f'%({k})s' for k in columns)  # 对应值的占位符拼接
    sql = f'INSERT INTO {table_name}({cols}) VALUES({val_cols})'
    return sql


def insert_or_update_sql(table_name: str, data: dict, duplicate_ignore_columns: list = None) -> str:
    return insert_or_update_sql_by_list(table_name, data.keys(), duplicate_ignore_columns)


def insert_or_update_sql_by_list(table_name: str, columns, duplicate_ignore_columns: list = None) -> str:
    cols = ", ".join(f'`{k}`' for k in columns)  # 字段名拼接
    val_cols = ', '.join(f'%({k})s' for k in columns)  # 对应值的占位符拼接
    if duplicate_ignore_columns is None:
        duplicate_cols = ', '.join(f'`{k}`=VALUES(`{k}`)' for k in columns)
    else:
        duplicate_cols = ', '.join(f'`{k}`=VALUES(`{k}`)' for k in columns if k not in duplicate_ignore_columns)
    sql = f'INSERT INTO {table_name}({cols}) VALUES({val_cols}) ON DUPLICATE KEY UPDATE {duplicate_cols}'
    return sql


def replace_sql(table_name: str, data: dict) -> str:
    """
    生成新增或更新的sql
    :arg table_name 表名
    :arg data 数据
    :rtype: str 生成的sql
    """
    return replace_sq_by_list(table_name, data.keys())


def replace_sq_by_list(table_name: str, columns) -> str:
    cols = ", ".join(f'`{k}`' for k in columns)  # 字段名拼接
    val_cols = ', '.join(f'%({k})s' for k in columns)  # 对应值的占位符拼接
    sql = f'REPLACE INTO {table_name}({cols}) VALUES({val_cols})'
    return sql


if __name__ == '__main__':
    with Mysql() as db:
        r = db.select_count('user')
        print(r)
